USE [NANS21V]
GO

/****** Object:  StoredProcedure [dbo].[RekidaKirokuList]    Script Date: 04/15/2014 17:28:03 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[RekidaKirokuList]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[RekidaKirokuList]
GO

CREATE PROCEDURE [dbo].[RekidaKirokuList]
	@TAIKAINO int,
	@KYOGINO int,
	@SUBNO int
AS

	SELECT R.REKIDAINO,
		   C.COMMENTRYAKU, 
		   KY.KYOGIMEI,
		   dbo.Kiroku(R.KIROKU,KY.SYUMOKUKUBUN,KY.KIROKUKUBUN,R.SOKUTEI) AS KIROKU,--2014/05/14R.R.SOKUTEIでよいかしらべる
		   R.SENSYUMEI, 
		   R.HIDUKE,
		   KY.SYUMOKUKUBUN
	  FROM TBL_REKIDAI AS R,
		   TBL_COMMENT AS C,
			(
				SELECT KYO.KYOGIMEI AS KYOGIMEI
				,KYO.TAIKAINO AS TAIKAINO
				,KYO.KYOGINO AS KYOGINO
				,KM.SYUMOKUKUBUN
				,KM.KIROKUKUBUN
				FROM
					TBL_KYOGI AS kYO
						LEFT OUTER JOIN TBL_SYUMOKU_MASTER AS KM 
						ON KYO.SYUMOKUNO = KM.SYUMOKUNO
			)AS KY
	  WHERE R.TAIKAINO = @TAIKAINO
		AND R.KYOGINO = @KYOGINO
		AND R.SABUNO = @SUBNO
		AND R.COMMENTNO = C.COMMENTNO
		AND R.TAIKAINO = kY.TAIKAINO
		AND R.KYOGINO = kY.KYOGINO
	  ORDER BY R.COMMENTNO, R.HIDUKE
GO